package io.github.talelin.latticy.repository;

import io.github.talelin.latticy.entity.Job;
import io.github.talelin.latticy.vo.TeamJobVO;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;

import java.sql.Timestamp;
import java.util.Date;
import java.util.List;

public interface JobRepository extends JpaRepository<Job, Long> {

    Job findFirstById(Long id);


    @Query(value = "select \n" +
            "*, count(*) as count \n" +
            "from job\n" +
            "where\n" +
            "user_id in :userId \n" +
            "and delete_time is null \n" +
            "and DATEDIFF(create_time, :now) = 0\n", nativeQuery = true)
    List<TeamJobVO> findOtherUserJob(List<Long> userId, String now);

    @Query(value = "select count(*) as count from job \n" +
            "where delete_time is null \n" +
            "and user_id = :userId \n" +
            "and status = 0 \n" +
            "and online > 0 \n" +
            "and DATEDIFF(create_time, :now) = 0", nativeQuery = true)
    Integer findUnfinishCount(Long userId, String now);


    @Query(value = "SELECT \n" +
            "DATE_FORMAT(create_time,'%Y-%m-%d') as create_time \n" +
            "from \n" +
            "job \n" +
            "WHERE \n" +
            "user_id = :userId \n" +
            "AND \n" +
            "delete_time is null \n" +
            "and \n" +
            "create_time is not null \n" +
            "and \n" +
            "online > 0 \n" +
            "order by \n" +
            "create_time desc", nativeQuery = true)
    List<String> findCreateTime(@Param("userId") Long userId);

    Page<Job> findByUserIdOrderByCreateTimeDesc(Long userId, Pageable pageable);

    @Query(value = "select \n" +
            "*\n" +
            "from \n" +
            "job \n" +
            "where \n" +
            "type = :type\n" +
            "and \n" +
            "user_id = :userId\n" +
            "and \n" +
            "delete_time is null \n" +
            "and \n" +
            "online > 0 \n" +
            "and\n" +
            "DATEDIFF(create_time, :now) = 0 \n" +
            "order by \n" +
            "create_time desc", nativeQuery = true)
    List<Job> findJobsByDate(int type, Long userId, String now);

    @Query(value = "select create_time from job \n" +
            "where delete_time is null and online > 0 and status = 0 and description is null and user_id = :userId \n" +
            "order by create_time desc \n" +
            "limit 1", nativeQuery = true)
    Timestamp findLastCreateTime(Long userId);

}
